<?php
if (!defined("BASEPATH")) exit("No direct script access allowed");

class Subject extends MY_Model {

	function __construct() {
		parent::__construct();
		$this->tableName = "subject";
		$this->prefix = "sub";
	}

	function getSubject($where = NULL, $start = NULL, $limit = NULL, $fields = NULL, $query = NULL, $order = "subName", $dir = "ASC") {

		$fieldsQuery = Common::fieldsquery($fields, $query);
		$order = Common::coalesce(array($order, "subName"));
		$dir = Common::coalesce(array($dir, "ASC"));

		$qs =	"
					select
						a.sub_id, a.sub_sub_id, a.sub_name, a.sub_url_name, b.sub_name as parent_sub_name,
						count(distinct maj_id) as majCnt
					from
						".$this->db->dbprefix.$this->tableName." as a
						left join ".$this->db->dbprefix.$this->tableName." as b on b.sub_id = a.sub_sub_id and b.sub_del = 0
						left join ".$this->db->dbprefix."major on a.sub_id = maj_sub_id and maj_del = 0
						left join ".$this->db->dbprefix."institute on ins_id = maj_ins_id and ins_del = 0
						left join ".$this->db->dbprefix."city on city_id = ins_city_id and city_del = 0
						left join ".$this->db->dbprefix."country on cou_id = city_cou_id and cou_del = 0
						left join ".$this->db->dbprefix."continent on con_id = cou_con_id and con_del = 0
						left join ".$this->db->dbprefix."ent2cat on e2c_entity = 'MAJ' and maj_id = e2c_ent_id and e2c_del = 0 and coalesce(e2c_int, e2c_text) is not null
					where
						a.sub_del = 0 and (a.sub_sub_id is null or b.sub_id is not null) ".((!is_null($where))?(" and ".$where):(""))." ".(($fieldsQuery)?(" and (".$fieldsQuery.") "):(""))."
					group by
						a.sub_id, b.sub_id
					order by
						".(($order == "rand()")?($order):(Common::underscore($order)." ".$dir))."
					".((is_numeric($start))?(" limit ".$start.",".$limit):(""))."
					";
		$result = $this->db->query($qs);
		//Common::pr($this->db->last_query());

		$getSubject = array();
		if($result->num_rows()) {
			foreach ($result->result() as $row) {

				if($row->sub_sub_id) {
					$getSubject[$row->sub_sub_id]["subId"] = $row->sub_sub_id;
					$getSubject[$row->sub_sub_id]["subName"] = $row->parent_sub_name;
					$getSubject[$row->sub_sub_id]["children"][$row->sub_id] = array(
						"subId" => $row->sub_id,
						"subSubId" => $row->sub_sub_id,
						"subSubName" => $row->parent_sub_name,
						"subName" => $row->sub_name,
						"subUrlName" => $row->sub_url_name,
						"majCnt" => $row->majCnt,
					);
				} else {
					$getSubject[$row->sub_id]["subId"] = $row->sub_id;
					$getSubject[$row->sub_id]["subName"] = $row->sub_name;
				}

			}
		}

		return $getSubject;
	}

}